package com.mouxidea;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
//import java.util.ResourceBundle;

public class DBManager {

	static {
		// Load the JDBC driver
	    String driverName = "oracle.jdbc.driver.OracleDriver";
	    try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public List<String> getAllDsiKeyId(){
		Config config = Config.getInstance();
		List<String> idList = new ArrayList<String>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
		    conn = DriverManager.getConnection("jdbc:oracle:thin:@" + config.getDbIp() + ":" + config.getDbPort() + ":" + config.getDbName(), config.getDbUsername(), config.getDbPassword());
		    stmt = conn.createStatement();
		    
//		    String sql = "select distinct DSI_KEY_ID_ " +
//                         "from SDVIRDevPorts, KV_IRDevPorts " +
//                         "where dsi_key_id = DSI_KEY_ID_ " +
//                         "and TA_PERIOD >= to_date(to_char(current_date),'dd-mon-yy') " +
//                         "and TA_PERIOD < to_date(to_char(current_date+1),'dd-mon-yy') " +
//                         "and ifAdminStatus in (1, 2) " +
//                         "and node_name = 'OV-2950-1' ";
		    String sql = config.getSqlId();
		    
		    rs = stmt.executeQuery(sql);
		    while(rs.next()){
		    	String id = rs.getString("DSI_KEY_ID_");
		    	idList.add(id);
		    }
		    
		} catch (SQLException e) {
			e.printStackTrace();
		    // Could not connect to the database
		}finally {
			try{rs.close();}catch(Exception ignored){}
			try{stmt.close();}catch(Exception ignored){}
			try{conn.close();}catch(Exception ignored){}
		}
		
		return idList;
		    
	}
	
	
	public List<Record> getLatestRecords(String dsiKeyId, int numOfRecord){
		Config config = Config.getInstance();
		List<Record> recordList = new ArrayList<Record>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
		    conn = DriverManager.getConnection("jdbc:oracle:thin:@" + config.getDbIp() + ":" + config.getDbPort() + ":" + config.getDbName(), config.getDbUsername(), config.getDbPassword());
		    stmt = conn.createStatement();
		    
		    String sql = config.getSqlToday();
		    sql = sql.replace("%%ID%%", dsiKeyId);
		    sql = sql.replace("%%N%%", String.valueOf(numOfRecord));
		    //System.out.println("Executing SQL: " + sql);
		    rs = stmt.executeQuery(sql);
		    
		    while(rs.next()){
		    	Record record = new Record();
		    	
		    	record.setTaPeriod(rs.getString("TA_PERIOD"));
		    	record.setDsiKeyId(rs.getString("DSI_KEY_ID_"));
		    	record.setDsiTableKey(rs.getString("dsi_table_key"));
		    	record.setDsiTargetName(rs.getString("dsi_target_name"));
		    	record.setDsiDescr(rs.getString("dsi_descr"));
		    	record.setMaxInUtilizationF(rs.getDouble("Max In Utilization (F)"));
		    	record.setMaxOutUtilizationF(rs.getDouble("Max Out Utilization (F)"));
		    	record.setMaxUtilizationH(rs.getDouble("Max Utilization (H)"));
		    	record.setAvgInUtilizationF(rs.getDouble("Avg In Utilization (F)"));
		    	record.setAvgOutUtilizationF(rs.getDouble("Avg Out Utilization (F)"));
		    	record.setAvgUtilizationH(rs.getDouble("Avg Utilization (H)"));
		    	record.setUtilThreshold(rs.getDouble("UtilThreshold"));
		    	
		    	recordList.add(record);
		    }
		    
		} catch (SQLException e) {
			e.printStackTrace();
		    // Could not connect to the database
		}finally {
			try{rs.close();}catch(Exception ignored){}
			try{stmt.close();}catch(Exception ignored){}
			try{conn.close();}catch(Exception ignored){}
		}
		
		return recordList;
	}
	
	public List<Record> get1WkOldRecords(String dsiKeyId){
		Config config = Config.getInstance();
		List<Record> recordList = new ArrayList<Record>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
		    conn = DriverManager.getConnection("jdbc:oracle:thin:@" + config.getDbIp() + ":" + config.getDbPort() + ":" + config.getDbName(), config.getDbUsername(), config.getDbPassword());
		    stmt = conn.createStatement();
		    
		    String sql = config.getSql1Wk();
		    sql = sql.replace("%%ID%%", dsiKeyId);
		    //System.out.println("Executing SQL: " + sql);
		    rs = stmt.executeQuery(sql);
		    
		    while(rs.next()){
		    	Record record = new Record();
		    	
		    	record.setTaPeriod(rs.getString("TA_PERIOD"));
		    	record.setDsiKeyId(rs.getString("DSI_KEY_ID_"));
		    	record.setDsiTableKey(rs.getString("dsi_table_key"));
		    	record.setDsiTargetName(rs.getString("dsi_target_name"));
		    	record.setDsiDescr(rs.getString("dsi_descr"));
		    	record.setMaxInUtilizationF(rs.getDouble("Max In Utilization (F)"));
		    	record.setMaxOutUtilizationF(rs.getDouble("Max Out Utilization (F)"));
		    	record.setMaxUtilizationH(rs.getDouble("Max Utilization (H)"));
		    	record.setAvgInUtilizationF(rs.getDouble("Avg In Utilization (F)"));
		    	record.setAvgOutUtilizationF(rs.getDouble("Avg Out Utilization (F)"));
		    	record.setAvgUtilizationH(rs.getDouble("Avg Utilization (H)"));
		    	record.setUtilThreshold(rs.getDouble("UtilThreshold"));
		    	
		    	recordList.add(record);
		    }
		    
		} catch (SQLException e) {
			e.printStackTrace();
		    // Could not connect to the database
		}finally {
			try{rs.close();}catch(Exception ignored){}
			try{stmt.close();}catch(Exception ignored){}
			try{conn.close();}catch(Exception ignored){}
		}
		
		return recordList;
	}
	
	public List<Record> get1MthOldRecords(String dsiKeyId){
		Config config = Config.getInstance();
		List<Record> recordList = new ArrayList<Record>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
		    conn = DriverManager.getConnection("jdbc:oracle:thin:@" + config.getDbIp() + ":" + config.getDbPort() + ":" + config.getDbName(), config.getDbUsername(), config.getDbPassword());
		    stmt = conn.createStatement();
		    
		    String sql = config.getSql1Mth();
		    sql = sql.replace("%%ID%%", dsiKeyId);
		    //System.out.println("Executing SQL: " + sql);
		    rs = stmt.executeQuery(sql);
		    
		    while(rs.next()){
		    	Record record = new Record();
		    	
		    	record.setTaPeriod(rs.getString("TA_PERIOD"));
		    	record.setDsiKeyId(rs.getString("DSI_KEY_ID_"));
		    	record.setDsiTableKey(rs.getString("dsi_table_key"));
		    	record.setDsiTargetName(rs.getString("dsi_target_name"));
		    	record.setDsiDescr(rs.getString("dsi_descr"));
		    	record.setMaxInUtilizationF(rs.getDouble("Max In Utilization (F)"));
		    	record.setMaxOutUtilizationF(rs.getDouble("Max Out Utilization (F)"));
		    	record.setMaxUtilizationH(rs.getDouble("Max Utilization (H)"));
		    	record.setAvgInUtilizationF(rs.getDouble("Avg In Utilization (F)"));
		    	record.setAvgOutUtilizationF(rs.getDouble("Avg Out Utilization (F)"));
		    	record.setAvgUtilizationH(rs.getDouble("Avg Utilization (H)"));
		    	record.setUtilThreshold(rs.getDouble("UtilThreshold"));
		    	
		    	recordList.add(record);
		    }
		    
		} catch (SQLException e) {
			e.printStackTrace();
		    // Could not connect to the database
		}finally {
			try{rs.close();}catch(Exception ignored){}
			try{stmt.close();}catch(Exception ignored){}
			try{conn.close();}catch(Exception ignored){}
		}
		
		return recordList;
	}
	
	public List<Record> getCustomizedRecords(String dsiKeyId, String fromTime, String toTime){
		Config config = Config.getInstance();
		List<Record> recordList = new ArrayList<Record>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
		    conn = DriverManager.getConnection("jdbc:oracle:thin:@" + config.getDbIp() + ":" + config.getDbPort() + ":" + config.getDbName(), config.getDbUsername(), config.getDbPassword());
		    stmt = conn.createStatement();
		    
		    String sql = config.getSql1Mth();
		    sql = sql.replace("%%ID%%", dsiKeyId);
		    sql = sql.replace("%%FROM_TIME%%", fromTime);
		    sql = sql.replace("%%TO_TIME%%", toTime);
		    //System.out.println("Executing SQL: " + sql);
		    rs = stmt.executeQuery(sql);
		    
		    while(rs.next()){
		    	Record record = new Record();
		    	
		    	record.setTaPeriod(rs.getString("TA_PERIOD"));
		    	record.setDsiKeyId(rs.getString("DSI_KEY_ID_"));
		    	record.setDsiTableKey(rs.getString("dsi_table_key"));
		    	record.setDsiTargetName(rs.getString("dsi_target_name"));
		    	record.setDsiDescr(rs.getString("dsi_descr"));
		    	record.setMaxInUtilizationF(rs.getDouble("Max In Utilization (F)"));
		    	record.setMaxOutUtilizationF(rs.getDouble("Max Out Utilization (F)"));
		    	record.setMaxUtilizationH(rs.getDouble("Max Utilization (H)"));
		    	record.setAvgInUtilizationF(rs.getDouble("Avg In Utilization (F)"));
		    	record.setAvgOutUtilizationF(rs.getDouble("Avg Out Utilization (F)"));
		    	record.setAvgUtilizationH(rs.getDouble("Avg Utilization (H)"));
		    	record.setUtilThreshold(rs.getDouble("UtilThreshold"));
		    	
		    	recordList.add(record);
		    }
		    
		} catch (SQLException e) {
			e.printStackTrace();
		    // Could not connect to the database
		}finally {
			try{rs.close();}catch(Exception ignored){}
			try{stmt.close();}catch(Exception ignored){}
			try{conn.close();}catch(Exception ignored){}
		}
		
		return recordList;
	}
	
}
